package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;
import com.mbc.rmt.data.CashPaymentBySentDataList;
import com.mbc.rmt.entities.CashPaymentBySent;
import com.mbc.rmt.entities.CashPaymentBySentDetail;

public class CashPaymentBySentDAOImpls extends DAO<CashPaymentBySent> implements CashPaymentBySentDAO{

	public CashPaymentBySentDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "CashPaymentBySent";
	}
	@Override
	public void create(CashPaymentBySent entity) throws SQLException {
		query = "insert into " + table_name + " ( SlipID, SlipCode, StaffID, StaffName, Date, Time, ReceivedBranchID, AgentID, AgentName, PaidBranchID, PaymentAmount) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setLong(i++, entity.getCashPaymentBySent_id());
		pstmt.setString(i++, entity.getSlip_code());
		pstmt.setLong(i++, entity.getStaff_id());
		pstmt.setString(i++, entity.getStaff_name());
		pstmt.setDate(i++,new java.sql.Date( entity.getDate().getTime()));
		pstmt.setString(i++, entity.getTime());
		pstmt.setLong(i++, entity.getReceivedBranch_id());
		pstmt.setLong(i++, entity.getAgent_id());
		pstmt.setString(i++, entity.getAgent_name());
		pstmt.setLong(i++, entity.getPaidBranch_id());
		pstmt.setDouble(i++, entity.getPaymentAmount());

		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void update(CashPaymentBySent entity) throws SQLException {
		query = "update " + table_name + " set  SlipCode = ?, StaffID = ?, StaffName = ?, Date = ?, Time = ?, ReceivedBranchID = ?, AgentID = ?, AgentName = ?, PaidBranchID = ?, PaymentAmount = ?" 
				+ " where SlipID = " + entity.getCashPaymentBySent_id();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getSlip_code());
		pstmt.setLong(i++, entity.getStaff_id());
		pstmt.setString(i++, entity.getStaff_name());
		pstmt.setDate(i++,new java.sql.Date( entity.getDate().getTime()));
		pstmt.setString(i++, entity.getTime());
		pstmt.setLong(i++, entity.getReceivedBranch_id());
		pstmt.setLong(i++, entity.getAgent_id());
		pstmt.setString(i++, entity.getAgent_name());
		pstmt.setLong(i++, entity.getPaidBranch_id());
		pstmt.setDouble(i++, entity.getPaymentAmount());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where SlipID = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public CashPaymentBySent find(long key) throws SQLException {
		CashPaymentBySent l_CashPaymentBySent = null;
		query = getSelectQuery() + " where SlipID = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_CashPaymentBySent = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_CashPaymentBySent;
	}

	@Override
	public ArrayList<CashPaymentBySent> findAll() throws SQLException {
		ArrayList<CashPaymentBySent> l_CashPaymentBySentList = new ArrayList<CashPaymentBySent>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_CashPaymentBySentList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_CashPaymentBySentList;
	}

	@Override
	public CashPaymentBySentDataList findCashPaymentBySents(CriteriaParams cri)
			throws SQLException {
		CashPaymentBySentDataList l_DataList = new CashPaymentBySentDataList();
		ArrayList<CashPaymentBySent> l_entityList = new ArrayList<CashPaymentBySent>();
		String l_cri = " or SlipCode like '%" + cri.getKeyword() + "%'" +
				" or StaffID like '%" + cri.getKeyword() + "%'"+
				" or StaffName like '%" + cri.getKeyword() + "%'"+
				" or Date like '%" + cri.getKeyword() + "%'" + 
				" or Time like '%" + cri.getKeyword() + "%'" +
				" or ReceivedBranchID like '%" + cri.getKeyword() + "%'" + 
				" or AgentID like '%" + cri.getKeyword() + "%'" +
				" or AgentName like '%" + cri.getKeyword() + "%'" +
				" or PaidBranchID like '%" + cri.getKeyword() + "%'" +
				" or PaymentAmount like '%" + cri.getKeyword() + "%'";
		
		query = "set rowcount " + cri.getSize() +
			" select * from (select * from " + table_name +
			" where 1 = 1" + l_cri + ") " + table_name +
			 + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()) {
			do {	
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(SlipID) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public ArrayList<CashPaymentBySentDetail> findCashPaymentBySentDetails(
			long cashPaymentBySentID) throws SQLException {
		ArrayList<CashPaymentBySentDetail> l_list = new ArrayList<CashPaymentBySentDetail>();

		query = "select branchchangedetailID,bcd.branchchangeID,bcd.userID,u.LoginId, u.UserName Name, "+
					"bcd.frombranchID,fbc.BranchCode fromBranchCode,bcd.tobranchID, "+
					"tbc.BranchCode toBranchCode,bcd.remark "+
				"from BranchChangeDetail bcd "+
					"inner join [User] u on u.userID = bcd.userID "+
					"inner join Branch fbc on bcd.frombranchID= fbc.BranchId "+
					"inner join Branch tbc on bcd.tobranchID= tbc.BranchId " + 
					"Where bcd.branchchangeID="+ cashPaymentBySentID ; 


		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){

			do{
				l_list.add(readRecords(rs));
			}while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_list;
	}
	protected CashPaymentBySentDetail readRecords(ResultSet rs) throws SQLException {
		CashPaymentBySentDetail l_entity = new CashPaymentBySentDetail();

		l_entity.setCashPaymentBySentDetail_id(rs.getLong("CashPaymentBySentDetailID"));
		l_entity.setSlip_id(rs.getLong("SlipID"));
		l_entity.setInvoiceno(rs.getString("InvoiceNo"));
		l_entity.setReceiptno(rs.getString("ReceiptNo"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setCurrency(rs.getString("Currency"));
		l_entity.setAmount(rs.getDouble("Amount"));
		l_entity.setPaided(rs.getString("Paided"));
		l_entity.setReturn_code(rs.getString("ReturnStatus"));
		l_entity.setApply(rs.getString("Apply"));
		l_entity.setBalance(rs.getDouble("Balance"));
		l_entity.setTotalamount(rs.getDouble("TotalAmount"));
		
		return l_entity;
	}
	@Override
	protected CashPaymentBySent readRecord(ResultSet rs) throws SQLException {
		CashPaymentBySent l_entity = new CashPaymentBySent();
		l_entity.setCashPaymentBySent_id(rs.getLong("SlipID"));
		l_entity.setSlip_code(rs.getString("SlipCode"));
		l_entity.setStaff_id(rs.getLong("StaffID"));
		l_entity.setStaff_name(rs.getString("StaffName"));
		l_entity.setDate(rs.getDate("Date"));
		l_entity.setTime(rs.getString("Time"));
		l_entity.setReceivedBranch_id(rs.getLong("ReceivedBranchID"));
		l_entity.setAgent_id(rs.getLong("AgentID"));
		l_entity.setAgent_name(rs.getString("AgentName"));
		l_entity.setPaidBranch_id(rs.getLong("PaidBranchID "));
		l_entity.setPaymentAmount(rs.getDouble("PaymentAmount"));

		return l_entity;
	}
	@Override
	public CashPaymentBySent getCashPaymentBySent(String code)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}
